-- 确保 auth.users 表存在
do $$ 
begin
  if not exists (select from pg_catalog.pg_tables 
    where schemaname = 'auth' and tablename = 'users') then
    raise exception 'auth.users table does not exist';
  end if;
end $$;

-- 删除现有的 profiles 表（如果存在）
drop table if exists public.profiles cascade;

-- 重新创建 profiles 表，使用更安全的约束方式
create table if not exists public.profiles (
  id uuid not null,
  nickname text,
  avatar_url text,
  updated_at timestamp with time zone default timezone('utc'::text, now()),
  primary key (id),
  constraint fk_user
    foreign key (id)
    references auth.users(id)
    on delete cascade
    deferrable initially immediate
);

-- 修改触发器函数以包含错误处理
create or replace function public.handle_new_user()
returns trigger 
security definer
set search_path = public
language plpgsql
as $$
begin
  -- 增加延迟以确保 auth.users 记录完全创建
  perform pg_sleep(0.1);
  
  -- 检查用户是否存在
  if not exists (select 1 from auth.users where id = new.id) then
    raise exception 'User with id % does not exist in auth.users', new.id;
  end if;

  -- 尝试创建 profile
  begin
    insert into public.profiles (id)
    values (new.id)
    on conflict (id) do nothing;
  exception when others then
    raise notice 'Error creating profile for user %: %', new.id, sqlerrm;
    return null;
  end;
  
  return new;
end;
$$;

-- 删除现有触发器
drop trigger if exists on_auth_user_created on auth.users;

-- 重新创建触发器，增加延迟
create trigger on_auth_user_created
  after insert on auth.users
  for each row
  execute procedure public.handle_new_user();

-- 创建辅助函数用于手动同步profiles
create or replace function public.sync_missing_profiles()
returns void
language plpgsql
security definer
as $$
begin
  insert into public.profiles (id)
  select id from auth.users u
  where not exists (
    select 1 from public.profiles p
    where p.id = u.id
  );
end;
$$;

-- 重置并重新设置RLS策略
alter table public.profiles enable row level security;
drop policy if exists "Public profiles are viewable by everyone." on profiles;
drop policy if exists "Users can insert their own profile." on profiles;
drop policy if exists "Users can update own profile." on profiles;

-- 重置profiles表的RLS策略
drop policy if exists "Enable insert for authenticated users only" on profiles;
drop policy if exists "Enable read access for all users" on profiles;
drop policy if exists "Enable update for users based on id" on profiles;

-- 修改profiles表的RLS策略
create policy "Enable insert access for users"
  on profiles for insert
  with check (true);  -- 允许任何插入操作

create policy "Enable read access for users"
  on profiles for select
  using (true);

create policy "Enable update access for users"
  on profiles for update
  using (auth.uid() = id);

-- 文档表
create table if not exists public.documents (
  id uuid default uuid_generate_v4() primary key,
  title text not null,
  content text,
  created_by uuid references auth.users not null,
  created_at timestamp with time zone default timezone('utc'::text, now()) not null,
  updated_at timestamp with time zone default timezone('utc'::text, now()) not null,
  status text default 'draft' check (status in ('draft', 'published', 'archived')),
  tags text[]
);

-- 重置文档表的RLS策略
drop policy if exists "Users can view their own documents" on documents;
drop policy if exists "Users can create their own documents" on documents;
drop policy if exists "Users can update their own documents" on documents;
drop policy if exists "Users can delete their own documents" on documents;

-- 文档表RLS策略
alter table public.documents enable row level security;
create policy "Users can view their own documents"
  on documents for select
  using ( auth.uid() = created_by );
create policy "Users can create their own documents"
  on documents for insert
  with check ( auth.uid() = created_by );
create policy "Users can update their own documents"
  on documents for update
  using ( auth.uid() = created_by );
create policy "Users can delete their own documents"
  on documents for delete
  using ( auth.uid() = created_by );

-- 文档评论表
create table if not exists public.comments (
  id uuid default uuid_generate_v4() primary key,
  document_id uuid references public.documents on delete cascade not null,
  content text not null,
  created_by uuid references auth.users not null,
  created_at timestamp with time zone default timezone('utc'::text, now()) not null,
  updated_at timestamp with time zone default timezone('utc'::text, now()) not null
);

-- 重置评论表的RLS策略
drop policy if exists "Users can view comments on accessible documents" on comments;
drop policy if exists "Users can create comments on accessible documents" on comments;

-- 评论表RLS策略
alter table public.comments enable row level security;
create policy "Users can view comments on accessible documents"
  on comments for select
  using ( 
    exists (
      select 1 from documents
      where id = comments.document_id
      and created_by = auth.uid()
    )
  );
create policy "Users can create comments on accessible documents"
  on comments for insert
  with check (
    auth.uid() = created_by and
    exists (
      select 1 from documents
      where id = document_id
      and created_by = auth.uid()
    )
  );

-- 创建更新时间触发器函数
create or replace function public.handle_updated_at()
returns trigger
language plpgsql
as $$
begin
  new.updated_at = timezone('utc'::text, now());
  return new;
end;
$$;

-- 删除现有触发器
drop trigger if exists handle_updated_at on public.documents;
drop trigger if exists handle_updated_at on public.comments;
drop trigger if exists handle_updated_at on public.profiles;

-- 为需要自动更新时间的表添加触发器
create trigger handle_updated_at
  before update
  on public.documents
  for each row
  execute procedure public.handle_updated_at();

create trigger handle_updated_at
  before update
  on public.comments
  for each row
  execute procedure public.handle_updated_at();

create trigger handle_updated_at
  before update
  on public.profiles
  for each row
  execute procedure public.handle_updated_at();

-- 创建教师表
create table if not exists public.teachers (
  id uuid default uuid_generate_v4() primary key,
  name varchar(50) not null,
  phone varchar(20),
  email varchar(100),
  subject varchar(50),
  status varchar(20) default 'active',
  created_at timestamp with time zone default timezone('utc'::text, now()),
  updated_at timestamp with time zone default timezone('utc'::text, now())
);

-- 创建教师-班级关联表
create table if not exists public.teacher_classes (
  id uuid default uuid_generate_v4() primary key,
  teacher_id uuid references teachers(id) on delete cascade,
  class_id varchar(20) references classes(id) on delete cascade,
  created_at timestamp with time zone default timezone('utc'::text, now()),
  unique(teacher_id, class_id)
);

-- 添加教师表的RLS策略
alter table public.teachers enable row level security;
create policy "Enable read access for authenticated users" on teachers
  for select using (auth.role() = 'authenticated');
create policy "Enable insert access for authenticated users" on teachers
  for insert with check (auth.role() = 'authenticated');
create policy "Enable update access for authenticated users" on teachers
  for update using (auth.role() = 'authenticated');
create policy "Enable delete access for authenticated users" on teachers
  for delete using (auth.role() = 'authenticated');

-- 添加教师-班级关联表的RLS策略
alter table public.teacher_classes enable row level security;
create policy "Enable all access for authenticated users" on teacher_classes
  for all using (auth.role() = 'authenticated');

-- 为教师表添加更新时间触发器
create trigger handle_updated_at
  before update on public.teachers
  for each row
  execute procedure public.handle_updated_at();
